﻿using Dapper;
using SIG.DAL.Dapper.Model;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;

namespace SIG.DAL.Dapper.Repositories
{

    public class PositionRepository
    {
        private DbConnection _connection;
        public Position GetItemByCode(string code)
        {
            Position result;
            using (_connection = Utilities.GetOpenConnection())
            {
               result = _connection.GetList<Position>(new { Code = code }).FirstOrDefault();               
            }
            return result;
        }

        public bool IsExistCode(string code)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                code = code.ToUpper();
                result = _connection.RecordCount<Position>("Where Code = @Code", new { Code = code });                
            }
            return result > 0;

        }
        public bool IsExistCode(string code,int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                code = code.ToUpper();
                result = _connection.RecordCount<Position>("Where Code = @Code and Id <> @Id", new { Code = code, Id=id });
            }
            return result > 0;
        }

        public IEnumerable<Position> GetItems()
        {
            IEnumerable<Position> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Position>();
            }
            return result;
        }

        public IEnumerable<Position> GetActiveItems()
        {
            IEnumerable<Position> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Position>(new { Active = true });
            }
            return result;
        }

        public IEnumerable<Position> GetListPagedItems(int pageIndex, int pageSize,string keyword, out int totalCount)
        {            
            IEnumerable<Position> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                totalCount = _connection.RecordCount<Position>("where Title like @Title", new { Title = likeKeyword });
                result = _connection.GetListPaged<Position>(pageIndex, pageSize, "where Title like @Title",  "Importance desc" , new { Title = likeKeyword });
            }
            return result;
        }

        public Position GetById(int id)
        {
            Position result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Get<Position>(id);
            }
            return result;
        }
        public int? Create(Position model)
        {
            int? result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Insert(model);
            }
            return result;
        }
        
        public int Update(Position model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Update(model);
            }
            return result;
        }
        public int Delete(Position model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete(model);
            }
            return result;
        }

        public int Delete(int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete<Position>(id);
            }
            return result;
        }


    }
}
